Parameters
Parameters provide a method where the user can interact with the report by selecting a value which can then determine the data being used. Parameters can also be used to pass information from one page to another to allow for drilldown type operations where a page can be opened with dynamic parameters taken from the component the user clicked on.
If your data is based on an SQL query you have two options of using parameters, both of which are detailed below. The difference is that having a parameter in a component works on the data that exists within the component, whereas the SQL query works by only extracting the data needed. If the total amount of data is not large, then a component filter will generally perform better, whereas if you are wanting to work with a small amount of data contained within a large database, then a SQL query parameter will minimise the amount of time taken to extract the data.
Using parameters in a component
Many components will have the ability to use parameters to filter the data being displayed. Those that will have a filter icon displayed when the component is clicked on, the second icon in the example below (the others being the edit and change type of component options).
Clicking on this will open the filter window. You can then drag from the dictionary on the left the item that you want to filter on which should be a field in the table that the component is based on. This will open up a separate window where you can set other parameters. The filter can be a fixed value or based on an expression that includes other fields in the database. If however you want it to be something that the users can interact with, you should use a variable. Tick the expression box and click on the edit icon next to the 'value' box to open the expression edit window. You can now add your variable to the expression by dragging it from the dictionary.
Add more filters as necessary and then click the preview window to see your filters at the top of the screen. Change the value and click 'Submit' to update the data in the component.
Using parameters in a SQL query
If your component is using data based on a SQL query data connection, then you have another option of using a variable to filter the SQL query so only data needed is returned. This does mean that each change of parameter requires a new SQL query to be run, but if you are working with a small amount of data from a much larger database this method is likely to be more efficient.
You will still need to create a variable as described in that section of the help. You can then edit the table you are using and click on the 'Add Parameter' option (the third one along in this section of the edit screen).
The 'Expression' should be the name of the variable. This variable will then need to be added to the SQL query as a 'where' clause where it can be referred to by preceeding the name with an '@' symbol. So in the above example where the parameter name is Invoice_No, and the data field we are filtering on is 'invoice_number', the SQL would look like this:
select * from demo."purchase_invoice"
where invoice_number = @Invoice_No
Click 'OK' to save the changes and then check the preview to show your parameter and test changing the values.
Creating Interactions with Parameters
Interactions are where the user clicks on a component, or a specific part of a component and an action occurs such as taking the user to a different page or following a hyperlink. This can also be used to provide drilldown functionality. To build an interaction of this type you will first have to have created your main dashboard or report (the source) and also another page that will show the drilldown details (the target).
With the source component selected click the 'Interactions' icon at the top of the screen. If your component is a tabular type then you will have to click the 'Run Fields Editor' option to set the interactions for the individual fields of the table. You will need to select 'Show Dashboard' (or report) for the mode and select your target page. Create a new parameter and give it a meaningful name and edit the value box to drag across the field being filtered on in the target page.
You can now go to your target page and create a parameter filter including the parameter name as part of the 'Value' expression.
This should now provide the parameter to the target report when the relevant item is clicked on the source report. As the parameters are not variables they are hidden from the user and passed to the target report in the background.
Interactions with a SQL Query
If your data source is compatible and you have created a SQL query filter rather than a report filter, you can also use this in an interaction. To do this:
- You will need a variable. You can remove the 'Request from user' option when creating the variable but make sure 'Allow use as a SQL parameter' is ticked.
- When you create the interaction give it a name matching exactly the variable you have created.
- In your SQL query make sure the expression is exactly the same as the variable name.
- As above, preceed the parameter name with an '@' symbol in the SQL query.
So in the below example of a data source query, Var1 is the variable name and the interaction name from the source report.
Using a SQL query parameter is particularly recommended for drilldown type interactions where you may be pulling back a single record from a much larger database.